package com.bringway.p2p.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.framework.util.RequestUtils;
import com.bringway.p2p.dao.ZqzrsqDao;

import net.sf.json.JSONObject;
@Repository
public class ZqzrsqDaoImpl extends P2PJdbcDao implements ZqzrsqDao {
	Logger logger=Logger.getLogger(ZqzrsqDaoImpl.class);

	@Override
	public List<Map<String, Object>> queryZqzrsqList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String czrr = params.optString("czrr");
		String cshr = params.optString("cshr");
		String csqzt = params.optString("csqzt");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		sql.append("SELECT T1.ID,T1.ZR_SQ_USERID,T1.ZR_SQ_TZJE,T1.ZR_SYF,date_format(T1.ZR_SQ_DATE,'%Y-%m-%d') ZR_SQ_DATE,T1.ZR_SQ_CONTENT,T1.ZR_SH_FM_USERID,date_format(T1.ZR_SH_DATE,'%Y-%m-%d') ZR_SH_DATE,T1.ZR_SH_CONTENT,T1.ZR_SQ_STATE,T1.REF_TZRXX_ID");
		sql.append(" ,T2.ZQID,T2.TCJE,date_format(T2.TZSJ,'%Y-%m-%d') TZSJ,T2.TCZT,T2.HKZT,date_format(T2.JXRQ,'%Y-%m-%d') JXRQ,T2.TZSY,T2.TZ_SOURCE,T2.POS_CODE,T2.HTBH FROM t_zhfw_tzrxx_zqzr_sq T1");
		sql.append(" LEFT JOIN t_zhfw_tzrxx T2 ON T1.REF_TZRXX_ID=T2.ID WHERE 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(T1.ZR_SQ_DATE,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(T1.ZR_SQ_DATE,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(czrr)){
			sql.append(" AND T1.ZR_SQ_USERID like "+ "'%"+czrr+"%'");
		}
		if(StringUtils.isNotEmpty(cshr)){
			sql.append(" AND T1.ZR_SH_FM_USERID like "+ "'%"+cshr+"%'");
		}
		if(StringUtils.isNotEmpty(csqzt)){
			sql.append(" AND ZR_SQ_STATE =:csqzt");
			paramMap.put("csqzt", csqzt);
		}
		sql.append(" order by T1.ID desc LIMIT :START,:LIMIT");
		logger.info("ZqzrsqDaoImpl sql is "+sql.toString());
		logger.info("ZqzrsqDaoImpl paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getZqzrsqCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String czrr = params.optString("czrr");
		String cshr = params.optString("cshr");
		String csqzt = params.optString("csqzt");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		sql.append("SELECT COUNT(*) FROM t_zhfw_tzrxx_zqzr_sq T1");
		sql.append(" LEFT JOIN t_zhfw_tzrxx T2 ON T1.REF_TZRXX_ID=T2.ID WHERE 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(T1.ZR_SQ_DATE,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(T1.ZR_SQ_DATE,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(czrr)){
			sql.append(" AND T1.ZR_SQ_USERID like "+ "'%"+czrr+"%'");
		}
		if(StringUtils.isNotEmpty(cshr)){
			sql.append(" AND T1.ZR_SH_FM_USERID like "+ "'%"+cshr+"%'");
		}
		if(StringUtils.isNotEmpty(csqzt)){
			sql.append(" AND ZR_SQ_STATE =:csqzt");
			paramMap.put("csqzt", csqzt);
		}
		logger.info("getZqzrsqCount sql is "+sql.toString());
		logger.info("getZqzrsqCount paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
	}

	@Override
	public Map<String, Object> getZqzrsqInfo(JSONObject params) {
		String id=params.optString("sqid");
		String sql="select ID, ZR_SQ_USERID, ZR_SQ_TZJE, ZR_SYF, ZR_SQ_DATE, ZR_SQ_CONTENT, ZR_SH_FM_USERID, ZR_SH_DATE, ZR_SH_CONTENT, ZR_SQ_STATE, REF_TZRXX_ID FROM t_zhfw_tzrxx_zqzr_sq WHERE ID=:ID ";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("ID", id);
		logger.info("getZqzrsqInfo sql is "+sql);
		logger.info("getZqzrsqInfo paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForMap(sql, paramMap);
	}

	@Override
	public int updateZqzrsq(JSONObject params) {
		String sqid=params.optString("sqid");
		String shr=params.optString("shr");
		String shzt=params.optString("shzt");
		String shbz=params.optString("shbz");
		String sql="update t_zhfw_tzrxx_zqzr_sq set ZR_SH_FM_USERID=:shr,ZR_SH_DATE=now(),ZR_SH_CONTENT=:shbz,ZR_SQ_STATE=:shzt where id=:sqid";
		Map<String, String> paramMap=new HashMap<>();
		paramMap.put("sqid", sqid);
		paramMap.put("shr", shr);
		paramMap.put("shzt", shzt);
		paramMap.put("shbz", shbz);
		paramMap.put("sql", sql);
		logger.info("updateZqzrsq sql is "+sql);
		logger.info("updateZqzrsq paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public int insertZqzrsq(JSONObject params) {
		String uuid=this.getUUId();
		String tzid=params.optString("tzid");
		String zrr=params.optString("zrr");
		String zrje=params.optString("zrje");
		//String zrkssj=params.optString("zrkssj");
		//String zrjssj=params.optString("zrjssj");
		String zrsxf=params.optString("zrsxf");
		String sqbz=params.optString("sqbz");
		StringBuffer sql=new StringBuffer();
		sql.append("insert into t_zhfw_tzrxx_zqzr_sq(ID, ZR_SQ_USERID, ZR_SQ_TZJE, ZR_SYF, ZR_SQ_DATE, ZR_SQ_CONTENT, ZR_SQ_STATE, REF_TZRXX_ID)");
		sql.append(" values(:uuid,:zrr,:zrje,:zrsxf,now(),:sqbz,'01',:tzid)");
		Map<String, String> paramMap=new HashMap<>();
		paramMap.put("uuid", uuid);
		paramMap.put("zrr", zrr);
		paramMap.put("zrje", zrje);
		paramMap.put("zrsxf", zrsxf);
		paramMap.put("sqbz", sqbz);
		paramMap.put("tzid", tzid);
		logger.info("insertZqzrsq sql is "+sql.toString());
		logger.info("insertZqzrsq paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
	}

}
